﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
namespace Entity
{
    public class DataBaseAccess
    {
        private static  String STRING_CONNECT =       " Server = COLDBOY-MSI ;"
                                                    + " Database = CVHT_HDDT;"
                                                    + " Integrated Security = True";
        private SqlCommand sqlCommand;
        private SqlDataAdapter sqlDataAdapter;
        private SqlDataReader sqlDataReader;
        private SqlConnection sqlConnection;
        //private DataSet dataSet;
        public DataBaseAccess()
        {
            this.sqlConnection = new SqlConnection(STRING_CONNECT);
        }
        public void openConnect()
        {
            try{
            
                if (sqlConnection == null)
                {
                    sqlConnection = new SqlConnection(STRING_CONNECT);
                    sqlConnection.Open();
            
                }

                else if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection = new SqlConnection(STRING_CONNECT);
                    sqlConnection.Open();
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show("Cannot Connect" + ex.Message);
            }
        }

        public void closeConnect()
        {
            try
            {
                if (sqlConnection.State == ConnectionState.Open)
                {
                    sqlConnection.Close();
                  //  sqlConnection.Dispose();
                }
                else return;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Close Connect " + ex.Message);
            }
        }

        
        ////////////////////////////////////////////////////////////////////////////////

        public int Test(string excuteString)
        {
            int i = 0;
            try
            {
                
                this.openConnect();
                this.sqlCommand = new SqlCommand(excuteString, this.sqlConnection);
                i = (int)(this.sqlCommand.ExecuteScalar());
                sqlConnection.Close();
                this.closeConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Excute \n" + ex.Message);
                sqlConnection.Close();
            }
            return i;
        }



        public void excuteNonQuery(string excuteString)
        {
            try
           {
                this.openConnect();
                this.sqlCommand = new SqlCommand(excuteString, this.sqlConnection);
                this.sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                this.closeConnect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error ExcuteNonQuery \n" + ex.Message) ;
                this.closeConnect();
            
            }
           
        }

        public object excuteScalar(string excuteString)
        {
            object Ketqua = null;
            try
            {

                this.openConnect();
                this.sqlCommand = new SqlCommand(excuteString, this.sqlConnection);
                Ketqua = sqlCommand.ExecuteScalar();
               
                this.closeConnect();
     
            }
            catch (Exception ex)
            {
                MessageBox.Show("Loi Scalar \n "+ ex.Message);
                this.closeConnect();
            }
            return Ketqua;
        }


        public String excuteReader(string excuteString)
        {
            string Ketqua = "";
            try
            {

                this.openConnect();
                SqlCommand command = new SqlCommand(excuteString, this.sqlConnection);
                sqlDataReader = command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    Ketqua = sqlDataReader[0].ToString();
                }
                this.closeConnect();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Loi Reader \n " + ex.Message);
                this.closeConnect();
            }
            return Ketqua;
        }

        public void addto_Combobox(ComboBox cb, string excuteString)
        {
            try
            {
               
                this.openConnect();
                SqlCommand Command = new SqlCommand(excuteString, this.sqlConnection);
                sqlDataReader = Command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    ListItem listItem = new ListItem();
                    listItem.value = sqlDataReader.GetString(0);
                    listItem.name = sqlDataReader.GetString(1);
                    cb.Items.Add(listItem);
                }
                cb.DisplayMember = "Name";
                cb.ValueMember = "Value";
                sqlDataReader.Close();
                sqlDataReader.Dispose();
                this.closeConnect();
            }
           catch(Exception ex){
                MessageBox.Show(ex.Message);
                this.closeConnect();
           }
        }



    /*    public void addtoDataGrid(DataGridView dataGrid, string excuteString)
        {
            try
            {
                this.dataSet = new DataSet();
                this.sqlDataAdapter = new SqlDataAdapter(excuteString, this.sqlConnection);
                sqlDataAdapter.Fill(dataSet, "Ten Bang");
                dataGrid.DataSource = dataSet.Tables[0];
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
*/
        public DataTable getDataTable(String excuteString)
        {
            DataTable dataTable = new DataTable();
            try
            {
                
                this.sqlDataAdapter = new SqlDataAdapter(excuteString, this.sqlConnection);
                sqlDataAdapter.Fill(dataTable);
                sqlDataAdapter.Dispose();
            }
                
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dataTable;
        }

        public int updateDataTable(String excuteString, DataTable dataTable)
        {
            int effect = 0;
            try
            {
                sqlCommand = new SqlCommand();
                sqlCommand.CommandText = excuteString;
                sqlCommand.Connection = sqlConnection;
                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
                effect = sqlDataAdapter.Update(dataTable);
                sqlDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return effect;
        }

      
        public void loadlen_Combobox(ComboBox cb, string sqlString)
        {

            cb.Items.Clear();
            this.openConnect();
            this.sqlCommand = new SqlCommand(sqlString, this.sqlConnection);
            this.sqlDataReader = this.sqlCommand.ExecuteReader();
            while (this.sqlDataReader.Read())
            {
                cb.Items.Add(sqlDataReader[0].ToString());
            }
            this.closeConnect();
            //            this.DongKetnoi();
        }

      
      /*    

        public string LoadLenLable(string sqlstring)
        {
            string Ketqua = "";
            try
            {

                //this.MoKetnoi();
                this.Connecttion.Open();
                this.Command = new SqlCommand(sqlstring, this.Connecttion);
                this.Reader = Command.ExecuteReader();
                while (Reader.Read())
                {
                    Ketqua = Reader[0].ToString();
                }
                //this.DongKetnoi();
                this.Connecttion.Close();
                return Ketqua;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                this.Connecttion.Close();
            }
            return Ketqua;
        }

        public int LayGiaTri(string sqlstring, string dulieuvao)
        {
            // this.MoKetnoi();
            this.Connecttion.Open();
            string str = "";
            this.Command = new SqlCommand(sqlstring, this.Connecttion);
            this.Reader = this.Command.ExecuteReader();
            //this.DongKetnoi();
            this.Connecttion.Close();
            while (Reader.Read())
            {
                if (dulieuvao == Reader[0].ToString())
                {
                    str = Reader[1].ToString();
                }
            }
            int n = int.Parse(str);
            return n;
        }

        public bool Kiemtra(string sqlstring)
        {
            //this.MoKetnoi();
            this.Connecttion.Open();
            this.Command = new SqlCommand(sqlstring, this.Connecttion);
            int n = (int)this.Command.ExecuteScalar();
            //this.DongKetnoi();
            this.Connecttion.Close();
            if (n > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        

        public void LoadMaLenComboBox(ComboBox cb, string sqlstring)
        {
            try
            {
                this.Connecttion.Open();
                SqlCommand Command = new SqlCommand(sqlstring, this.Connecttion);
                SqlDataReader dtr;
                dtr = Command.ExecuteReader();
                while (dtr.Read())
                {
                    ListItem listitem = new ListItem();
                    listitem.Name = dtr.GetString(0);
                    cb.Items.Add(listitem);
                }
                cb.DisplayMember = "Name";
                cb.ValueMember = "Name";
                dtr.Close();
                this.Connecttion.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public int GiaTri_ThamSo(string sqlstring)
        {
            //this.MoKetnoi();
            this.Connecttion.Open();
            this.Command = new SqlCommand(sqlstring, this.Connecttion);
            int n = (int)this.Command.ExecuteScalar();
            //this.DongKetnoi();
            this.Connecttion.Close();
            return n;
        }
        ////////////////////////////////////////////////////////////////////////////////
        */








    }
}
